﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.DBM;

namespace KPIS.GERP.GHRMS.BLL
{
    public class PromoteEmployeeBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable SelectPromotionEmp(int PrmYear, int prmPeriod, int empTypeSeq, Nullable<int> JobSiteSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@prm_year", PrmYear);
                dbManager.AddParameters(1, "@prm_period", prmPeriod);
                dbManager.AddParameters(2, "@emp_type_seq", empTypeSeq);
                dbManager.AddParameters(3, "@job_site_seq", JobSiteSeq);
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                string strSQL;

                strSQL = " SELECT pe.PRM_EMP_SEQ, e.EMP_SEQ, p.POS_REF"
                             + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS EMP_TEXT"
                             + ", IFNULL(dt.SCORE, pe.SCORE) AS SCORE, pe.APR_DTLS_SEQ, p.POS_NAME, c.CLASS_NAME"
                             + ", case pe.promote when 1 then 'เลื่อน' when 0 then 'ไม่เลื่อน' else '' end as PROMOTE_NAME"
                             + ", pe.PROMOTE, pe.APR_JOB_SITE_SEQ, aj.JOB_SITE_NAME AS APR_JOB_SITE_NAME"
                             + ", pe.PRM_JOB_SITE_SEQ, pj.JOB_SITE_NAME AS PRM_JOB_SITE_NAME"
                             + ", CASE WHEN (pe.SCORE >= sl.SCO_MIN AND pe.SCORE < sl.sco_max + 1)"
                                        + " THEN sl.SUB_SCO_LEVEL_NAME"
                                    + " ELSE 'ต่ำกว่าเกณฑ์' END AS SCORE_NAME"
                            + ", pe.REMARKS"
                         + " FROM HRM_PRM_PROMOTION_EMPLOYEES pe "
                         + " LEFT JOIN HRM_PS_EMPLOYEES e ON pe.EMP_SEQ = e.EMP_SEQ"
                         + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                         + " LEFT JOIN HRM_PRM_PROMOTIONS pr ON pe.PRM_SEQ = pr.PRM_SEQ"
                            + " AND pr.PRM_YEAR = @prm_year"
                            + " AND pr.PRM_PERIOD = @prm_period"
                         + " LEFT JOIN HRM_APR_APPRAISAL_DTLS dt ON pe.APR_DTLS_SEQ = dt.APR_DTLS_SEQ"
                         + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                         + " LEFT JOIN HRM_MP_JOB_SITES aj ON pe.APR_JOB_SITE_SEQ = aj.JOB_SITE_SEQ"
                         + " LEFT JOIN HRM_MP_JOB_SITES pj ON pe.PRM_JOB_SITE_SEQ = pj.JOB_SITE_SEQ"
                         + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                         + " LEFT JOIN HRM_PRM_SUB_SCORE_LEVELS sl ON pe.PRM_SEQ = sl.PRM_SEQ"
                            + " AND pe.PRM_JOB_SITE_SEQ = sl.JOB_SITE_SEQ"
                            + " AND (pe.SCORE >= sl.SCO_MIN AND pe.SCORE < sl.SCO_MAX + 1)"
                         + " WHERE pe.RECORD_STATUS not like @RecordStatus "
                            + " AND e.EMP_TYPE_SEQ = @emp_type_seq "
                            + (JobSiteSeq.HasValue ? " AND pe.APR_JOB_SITE_SEQ = @job_site_seq" : "")
                            + " ORDER BY e.CLASS_SEQ DESC, p.POS_REF + 1";

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0].Copy();
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void InsertPromotionEmp(int prm_seq, int emp_seq, int prm_job_site_seq, int apr_job_site_seq
                    , int promote, double score, double salary, int prm_base_seq, double percen_promote
                    , double amount, int apr_dtls_seq, string remarks, int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@prm_seq", prm_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@prm_job_site_seq", prm_job_site_seq);
                dbManager.AddParameters(3, "@apr_job_site_seq", apr_job_site_seq);
                dbManager.AddParameters(4, "@promote", promote);
                dbManager.AddParameters(5, "@score", score);
                dbManager.AddParameters(6, "@salary", salary);
                dbManager.AddParameters(7, "@prm_base_seq", prm_base_seq);
                dbManager.AddParameters(8, "@percen_promote", percen_promote);
                dbManager.AddParameters(9, "@amount", amount);
                dbManager.AddParameters(10, "@apr_dtls_seq", apr_dtls_seq);
                dbManager.AddParameters(11, "@remarks", remarks);
                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@created_by", created_by);
                dbManager.AddParameters(14, "@created_when", DateTime.Now);

                string strSQL = "INSERT INTO HRM_PRM_PROMOTION_EMPLOYEES"
                    + "(PRM_SEQ, EMP_SEQ, PRM_JOB_SITE_SEQ, APR_JOB_SITE_SEQ, PROMOTE"
                    + ", SCORE, PRM_BASE_SEQ, PERCEN_PROMOTE, AMOUNT, APR_DTLS_SEQ"
                    + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + "VALUES(@prm_seq, @emp_seq, @prm_job_site_seq, @apr_job_site_seq, @promote"
                    + ", @score, @prm_base_seq, @percen_promote, @amount, @apr_dtls_seq"
                    + ", @remarks, @record_status, @created_by, @created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw new Exception("{INSERT_HRM_PRM_PROMOTION_EMPLOYEES}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }
        public void InsertPromotionCommands(int cmd_seq, int prm_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@prm_seq", prm_seq);

                string strSQL = "CALL P_Auto_Insert_Cmd_Promotion(@cmd_seq, @prm_seq)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw new Exception("{INSERT_HRM_PS_COMMAND_DTLS}" + ex.Message);
            }
            finally
            {
                dbManager.Dispose();
            }

        }
        public void UpdatePromotionEmp(int prm_emp_seq, int prm_seq, int emp_seq, int prm_job_site_seq, int apr_job_site_seq
                    , int promote, double score, double salary, int prm_base_seq, double percen_promote
                    , double amount, int apr_dtls_seq, string remarks, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);

                dbManager.AddParameters(0, "@prm_emp_seq", prm_emp_seq);
                dbManager.AddParameters(1, "@prm_seq", prm_seq);
                dbManager.AddParameters(2, "@emp_seq", emp_seq);
                dbManager.AddParameters(3, "@prm_job_site_seq", prm_job_site_seq);
                dbManager.AddParameters(4, "@apr_job_site_seq", apr_job_site_seq);
                dbManager.AddParameters(5, "@promote", promote);
                dbManager.AddParameters(6, "@score", score);
                dbManager.AddParameters(7, "@salary", salary);
                dbManager.AddParameters(8, "@prm_base_seq", prm_base_seq);
                dbManager.AddParameters(9, "@percen_promote", percen_promote);
                dbManager.AddParameters(10, "@amount", amount);
                dbManager.AddParameters(11, "@apr_dtls_seq", apr_dtls_seq);
                dbManager.AddParameters(12, "@remarks", remarks);
                dbManager.AddParameters(13, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@updated_by", updated_by);
                dbManager.AddParameters(15, "@updated_when", DateTime.Now);

                string strSQL = " UPDATE HRM_PRM_PROMOTION_EMPLOYEES"
                    + " SET PRM_SEQ = @prm_seq"
                        + ", EMP_SEQ = @emp_seq"
                        + ", PRM_JOB_SITE_SEQ = @prm_job_site_seq"
                        + ", APR_JOB_SITE_SEQ = @apr_job_site_seq"
                        + ", PROMOTE = @promote"
                        + ", SCORE = @score"
                        //+ ", SALARY = @salary"
                        + ", PRM_BASE_SEQ = @prm_base_seq"
                        + ", PERCEN_PROMOTE = @percen_promote"
                        + ", AMOUNT = @amount"
                        + ", APR_DTLS_SEQ = @apr_dtls_seq"
                        + ", REMARKS = @remarks"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE PRM_EMP_SEQ =@prm_emp_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void DeletePromotionEmp(int prm_emp_seq, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@prm_emp_seq", prm_emp_seq);
                dbManager.AddParameters(1, "@updated_by", updated_by);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string sql;
                sql = " UPDATE HRM_PRM_PROMOTION_EMPLOYEES"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE PRM_EMP_SEQ =@prm_emp_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void InsertSelect(int emp_type_seq, int prm_seq, int job_site_seq, string CreatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(1, "@prm_seq", prm_seq);
                dbManager.AddParameters(2, "@job_site_seq", "%/" + job_site_seq.ToString() + "/%");
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(5, "@CreatedWhen", DateTime.Today);

                string strSQL = "INSERT INTO HRM_PRM_PROMOTION_EMPLOYEES"
                    + "(PRM_SEQ, EMP_SEQ, PRM_JOB_SITE_SEQ, APR_JOB_SITE_SEQ, PROMOTE"
                    + ", SCORE, SALARY, PRM_BASE_SEQ, PERCEN_PROMOTE, AMOUNT, APR_DTLS_SEQ"
                    + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " SELECT PRM_SEQ, EMP_SEQ"
                        + ", CASE WHEN l.JOB_SITE_LEVEL_NO > 3 THEN j.upper_job_site_seq" // เอาหน่วยงานระดับ สำนัก กอง
                            + " ELSE j.JOB_SITE_SEQ END AS PRM_JOB_SITE_SEQ"
                        + ", CASE WHEN l.JOB_SITE_LEVEL_NO > 3 THEN j.upper_job_site_seq"
                            + " ELSE j.JOB_SITE_SEQ END AS APR_JOB_SITE_SEQ"
                        + ", PROMOTE, SCORE, SALARY, PRM_BASE_SEQ, PERCEN_PROMOTE, AMOUNT"
                        + ", APR_DTLS_SEQ, t.REMARKS, t.RECORD_STATUS, t.CREATED_BY, t.CREATED_WHEN "
                    + " FROM("
                        + " select @prm_seq as PRM_SEQ, e.EMP_SEQ"
                            + ", ifnull(e.working_job_site_seq, p.job_site_seq) as JOB_SITE_SEQ"
                            + ", 0 as PROMOTE, 0 as SCORE, e.SALARY, null as PRM_BASE_SEQ, 0 as PERCEN_PROMOTE"
                            + ", 0 as AMOUNT, null as APR_DTLS_SEQ, null as REMARKS"
                            + ", @record_status as RECORD_STATUS, @CreatedBy as CREATED_BY, @CreatedWhen as CREATED_WHEN"
                        + " from hrm_ps_employees e"
                        + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                        + " where e.record_status = @record_status"
                            + " and e.emp_type_seq = @emp_type_seq"
                    + ")t "
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON t.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITE_LEVELS l ON j.JOB_SITE_LEVEL_SEQ = l.JOB_SITE_LEVEL_SEQ"
                    + " WHERE j.RECORD_STATUS = @record_status"
                        + " AND j.JOB_SITE_FULL_PATH LIKE @job_site_seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSelect(int MngTestSeq, int UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@MngTestSeq", MngTestSeq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);

                string strSQL;

                strSQL = "CALL P_Auto_Update_Prm_Employee(@MngTestSeq)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public int GetPromotion(int empTypeSeq, int BgYear, int Period)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@EmpTypeSeq", empTypeSeq);
                dbManager.AddParameters(1, "@BgYear", BgYear);
                dbManager.AddParameters(2, "@Period", Period);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = " SELECT PRM_SEQ"
                    + " FROM HRM_PRM_PROMOTIONS"
                    + " WHERE RECORD_STATUS = @RecordStatus"
                        + " AND EMP_TYPE_SEQ = @EmpTypeSeq"
                        + " AND PRM_YEAR = @BgYear"
                        + " AND PRM_PERIOD = @Period";
                 
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["PRM_SEQ"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public int GetSubScoreLevel(int PrmSeq, int JobSiteSeq, double Score)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@PrmSeq", PrmSeq);
                dbManager.AddParameters(1, "@JobSiteSeq", JobSiteSeq);
                dbManager.AddParameters(2, "@Score", Score);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = " SELECT SUB_SCO_LEVEL_SEQ"
                    + " FROM HRM_PRM_SUB_SCORE_LEVELS"
                        + " WHERE RECORD_STATUS = @RecordStatus"
                        + " AND PRM_SEQ = @PrmSeq"
                        + " AND JOB_SITE_SEQ = @JobSiteSeq"
                        + " AND (SCO_MIN <= @Score AND SCO_MAX + 1 > @Score)";

                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                return Convert.ToInt32(ds.Tables[0].Rows[0]["SUB_SCO_LEVEL_SEQ"].ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
